<?php
include_once dirname(__FILE__).'/../RDBConfig.php';
include_once dirname(__FILE__).'/../../interface/UserJoinProjectDAOInterface.php';
include_once dirname(__FILE__).'/../../object/UserJoinProject.php';
include_once dirname(__FILE__).'/UserDAO.php';
include_once dirname(__FILE__).'/ProjectDAO.php';

class UserJoinProjectDAO implements UserJoinProjectDAOInterface {
	
	public static $TABLE_NAME = 'USER_JOIN_PROJECT';
	public static $COL_PROJECT_PK = 'PROJECT_PK';
	public static $COL_USER_PK = 'USER_PK';
	public static $COL_ROLE = 'ROLE';
	
	public function __construct() {
	
	}
	
	/**
	 * Them mot dong vao bang 'USER_JOIN_PROJECT'.
	 * Tra ve so dong da them
	 * @see UserJoinProjectDAOInterface::insert()
	 * @param UserJoinProject $userJoinProject
	 * @return int
	 */
	public function insert($userJoinProject) {
		$table = self::$TABLE_NAME;
		$colProjectPK = self::$COL_PROJECT_PK;
		$colUserPK = self::$COL_USER_PK;
		$colRole = self::$COL_ROLE;
		
		// tao ket noi
		$rdbConfig = new RDBConfig(RDBConfig::$DEFAULT_HOST, RDBConfig::$DEFAULT_DBNAME,
				RDBConfig::$DEFAULT_USERNAME, RDBConfig::$DEFAULT_PASSWORD);
		$conn = RDBDAOFactory::createConnection($rdbConfig);
		
		// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
		$statement = <<<END
        INSERT INTO $table
        	($colProjectPK, $colUserPK, $colRole)
                VALUES (:project_pk, :user_pk, :role)
END;
		$sql = $conn->prepare($statement); // chuan bi cau truy van
		$data = array( // chuan bi du lieu
				':project_pk' => $userJoinProject->getProjectPK(),
				':user_pk' => $userJoinProject->getUserPK(),
				':role' => $userJoinProject->getRole() 
		);
		
		try {
			$conn->beginTransaction(); // bo che do tu dong commit
		
			$sql->execute($data); // do du lieu vao cau truy van
		
			$conn->commit(); // commit de cap nhat thay doi vao CSDL
		
		} catch (PDOException $excs) { // neu co loi thi cuon lai
			echo $excs->getMessage().'<br>';
			$conn->rollBack();
		}
		
		$conn = null; // free memory
		return $sql->rowCount(); // tra ve so dong da them
	}
	
	/**
	 * Xoa mot dong co PK tuong ung.
	 * Tra ve so dong da xoa.
	 * @see UserJoinProjectDAOInterface::delete()
	 * @param UserJoinProject $userJoinProject
	 * @return int
	 */
	public function delete($userJoinProject) {
		$table = self::$TABLE_NAME;
		$colProjectPK = self::$COL_PROJECT_PK;
		$colUserPK = self::$COL_USER_PK;
		$colRole = self::$COL_ROLE;
		
		// tao ket noi
		$rdbConfig = new RDBConfig(RDBConfig::$DEFAULT_HOST, RDBConfig::$DEFAULT_DBNAME,
				RDBConfig::$DEFAULT_USERNAME, RDBConfig::$DEFAULT_PASSWORD);
		$conn = RDBDAOFactory::createConnection($rdbConfig);
		
		// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
		$statement = <<<END
        DELETE FROM $table
        	WHERE $colProjectPK = :project_pk
        	AND $colUserPK = :user_pk
END;
		$sql = $conn->prepare($statement); // chuan bi cau truy van
		$data = array( // chuan bi du lieu
				':project_pk' => $userJoinProject->getProjectPK(),
				':user_pk' => $userJoinProject->getUserPK()
		);
		
		try {
			$conn->beginTransaction(); // bo che do tu dong commit
		
			$sql->execute($data); // do du lieu vao cau truy van
		
			$conn->commit(); // commit de cap nhat thay doi vao CSDL
		
		} catch (PDOException $excs) { // neu co loi thi cuon lai
			echo $excs->getMessage().'<br>';
			$conn->rollBack();
		}
		
		$conn = null; // free memory
		return $sql->rowCount(); // tra ve so dong da them
	}
	
	/**
	 * Tra ve cac Project ma User tham gia.
	 * @see UserJoinProjectDAOInterface::getProjectByUser()
	 * @param User $user
	 * @param int $role @see UserJoinProject
	 * @return Project (array)
	 */
	public function getProjectByUser($user, $role) {
		$table = self::$TABLE_NAME;
		$colProjectPK = self::$COL_PROJECT_PK;
		$colUserPK = self::$COL_USER_PK;
		$colRole = self::$COL_ROLE;
		
		$tableUser = UserDAO::$TABLE_NAME;
		
		$tableProject = ProjectDAO::$TABLE_NAME;
		$colProjectName = ProjectDAO::$COL_PROJECT_NAME;
		$colCreatedDate = ProjectDAO::$COL_CREATED_DATE;
		
		// tao ket noi
		$rdbConfig = new RDBConfig(RDBConfig::$DEFAULT_HOST, RDBConfig::$DEFAULT_DBNAME,
				RDBConfig::$DEFAULT_USERNAME, RDBConfig::$DEFAULT_PASSWORD);
		$conn = RDBDAOFactory::createConnection($rdbConfig);
		
		if($role == UserJoinProject::$ALL_ROLE) {
			// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
			$statement = <<<END
        SELECT a.$colProjectPK, a.$colProjectName, a.$colCreatedDate
        FROM $tableProject a, $tableUser b, $table c
        WHERE 	b.$colUserPK = c.$colUserPK
        	AND	a.$colProjectPK = c.$colProjectPK
        	AND	b.$colUserPK = :user_pk
END;
			
			$data = array( // chuan bi du lieu
					':user_pk' => $user->getUserPK()
			);
			
		} else {
			// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
			$statement = <<<END
        SELECT a.$colProjectPK, a.$colProjectName, a.$colCreatedDate
        FROM $tableProject a, $tableUser b, $table c
        WHERE 	b.$colUserPK = c.$colUserPK
        	AND	a.$colProjectPK = c.$colProjectPK
        	AND	b.$colUserPK = :user_pk
        	AND c.$colRole = :role
END;
			
			$data = array( // chuan bi du lieu
					':user_pk' => $user->getUserPK(),
					':role' => $role
			);
		}
		
		$sql = $conn->prepare($statement); // chuan bi cau truy van
		$projects = array();
		try {
			$sql->execute($data); // do du lieu vao cau truy van
			$sql->setFetchMode(PDO::FETCH_ASSOC);
			foreach ($sql->fetchAll() as $pro) {
				$projects[] = new Project($pro["$colProjectPK"], $pro["$colProjectName"],
						$pro["$colCreatedDate"]);
			}
		} catch (PDOException $excs) { // neu co loi thi cuon lai
			echo $excs->getMessage().'<br>';
		}
		$conn = null; // free memory
		
		return $projects;
	}
	
	/**
	 * Tra ve cac User tham gia Project.
	 * @see UserJoinProjectDAOInterface::getUserByProject()
	 * @param Project $project
	 * @param int $role @see UserJoinProject
	 * @return User (array)
	 */
	public function getUserByProject($project, $role) {
		$table = self::$TABLE_NAME;
		$colProjectPK = self::$COL_PROJECT_PK;
		$colUserPK = self::$COL_USER_PK;
		$colRole = self::$COL_ROLE;
		
		$tableUser = UserDAO::$TABLE_NAME;
		$colUserID = UserDAO::$COL_USER_ID;
		$colUserName = UserDAO::$COL_USER_NAME;
		$colPassword = UserDAO::$COL_PASSWORD;
		$colEmail = UserDAO::$COL_EMAIL;
		
		$tableProject = ProjectDAO::$TABLE_NAME;
		
		// tao ket noi
		$rdbConfig = new RDBConfig(RDBConfig::$DEFAULT_HOST, RDBConfig::$DEFAULT_DBNAME,
				RDBConfig::$DEFAULT_USERNAME, RDBConfig::$DEFAULT_PASSWORD);
		$conn = RDBDAOFactory::createConnection($rdbConfig);
		if($role == UserJoinProject::$ALL_ROLE) {
			// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
			$statement = <<<END
        SELECT a.$colUserPK, a.$colUserID, a.$colUserName, a.$colPassword, a.$colEmail
        FROM $tableUser a, $tableProject b, $table c
        WHERE 	a.$colUserPK = c.$colUserPK
        	AND	b.$colProjectPK = c.$colProjectPK
        	AND	b.$colProjectPK = :project_pk
END;
			$sql = $conn->prepare($statement); // chuan bi cau truy van
			$data = array( // chuan bi du lieu
					':project_pk' => $project->getProjectPK()
			);
			
		} else {
			// tao cau truy van (luu y chu END; cuoi khong co dau cach phia truoc!)
			$statement = <<<END
        SELECT a.$colUserPK, a.$colUserID, a.$colUserName, a.$colPassword, a.$colEmail
        FROM $tableUser a, $tableProject b, $table c
        WHERE 	a.$colUserPK = c.$colUserPK
        	AND	b.$colProjectPK = c.$colProjectPK
        	AND	b.$colProjectPK = :project_pk
        	AND c.$colRole = :role
END;
			$sql = $conn->prepare($statement); // chuan bi cau truy van
			$data = array( // chuan bi du lieu
					':project_pk' => $project->getProjectPK(),
					':role' => $role
			);
			
		}
				
		$users = array();
		try {
			$sql->execute($data); // do du lieu vao cau truy van
			$sql->setFetchMode(PDO::FETCH_ASSOC);
			foreach ($sql->fetchAll() as $user) {
				$users[] = new User($user["$colUserPK"],
						$user["$colUserID"], $user["$colUserName"], 
						$user["$colPassword"], $user["$colEmail"]);
			}
		} catch (PDOException $excs) { // neu co loi thi cuon lai
			echo $excs->getMessage().'<br>';
		}
		$conn = null; // free memory
		
		return $users;
	}
}
?>
